import streamlit as st
import pandas as pd
from db_utils import get_connection

def _get_all_customers_from_view(cursor):
    # 改为从视图查询，可以获取更丰富的信息
    cursor.execute("SELECT * FROM vw_customer_card ORDER BY customerID")
    return cursor.fetchall()

def _get_all_customers(cursor):
    cursor.execute("SELECT * FROM userInfo ORDER BY customerID")
    return cursor.fetchall()

# 客户管理
def manage_customers():
    st.header("客户信息管理")
    
    conn = get_connection()
    if not conn:
        return

    try:
        cursor = conn.cursor(dictionary=True)
        
        tab_view, tab_add, tab_edit, tab_delete, tab_search = st.tabs([
            "浏览客户", "新增客户", "修改客户信息", "删除客户", "查询客户"
        ])

        with tab_view:
            st.subheader("所有客户信息")
            customers = _get_all_customers(cursor)
            if customers:
                df_customers = pd.DataFrame(customers)
                st.dataframe(df_customers, use_container_width=True)
                csv = df_customers.to_csv(index=False).encode('utf-8')
                st.download_button(
                    label="导出客户数据为CSV",
                    data=csv,
                    file_name='customers.csv',
                    mime='text/csv',
                )
            else:
                st.info("当前没有客户信息。")

        with tab_add:
            st.subheader("新增客户 (将自动开立一张活期卡)")
            with st.form("new_customer_form", clear_on_submit=True):
                name = st.text_input("姓名")
                pid = st.text_input("身份证号 (18位)")
                phone = st.text_input("电话 (11位)")
                address = st.text_area("地址")
                submitted = st.form_submit_button("确认新增")
                if submitted:
                    if not (name and pid and phone):
                        st.warning("姓名、身份证号和电话为必填项。")
                    else:
                        try:
                            cursor.execute("INSERT INTO userInfo (customerName, PID, telephone, address) VALUES (%s, %s, %s, %s)",
                                           (name, pid, phone, address))
                            conn.commit()
                            st.success(f"客户 '{name}' 新增成功！系统已为其自动开立一张银行卡。")
                            st.rerun()
                        except Exception as e:
                            conn.rollback()
                            st.error(f"新增失败: {e}")
        
        with tab_edit:
            st.subheader("修改客户信息")
            customers = _get_all_customers(cursor)
            if customers:
                df_customers = pd.DataFrame(customers)
                customer_id_to_edit = st.selectbox("选择要修改的客户", options=df_customers['customerID'], format_func=lambda x: f"{df_customers.loc[df_customers['customerID']==x, 'customerName'].item()} (ID: {x})")
                current_customer = df_customers[df_customers['customerID'] == customer_id_to_edit].to_dict('records')[0]
                with st.form("edit_customer_form"):
                    name = st.text_input("新姓名", value=current_customer['customerName'])
                    phone = st.text_input("新电话", value=current_customer['telephone'])
                    address = st.text_area("新地址", value=current_customer['address'])
                    submitted = st.form_submit_button("确认修改")
                    if submitted:
                        try:
                            cursor.execute("UPDATE userInfo SET customerName=%s, telephone=%s, address=%s WHERE customerID=%s",
                                           (name, phone, address, customer_id_to_edit))
                            conn.commit()
                            st.success(f"客户(ID: {customer_id_to_edit})的信息修改成功！")
                            st.rerun()
                        except Exception as e:
                            conn.rollback()
                            st.error(f"修改失败: {e}")
            else:
                st.info("没有客户可供修改。")

        with tab_delete:
            st.subheader("删除客户")
            customers = _get_all_customers(cursor)
            if customers:
                df_customers = pd.DataFrame(customers)
                customer_id_to_delete = st.selectbox("选择要删除的客户", options=df_customers['customerID'], format_func=lambda x: f"{df_customers.loc[df_customers['customerID']==x, 'customerName'].item()} (ID: {x})", key="delete_select")
                st.warning(f"确定要删除客户 {df_customers.loc[df_customers['customerID']==customer_id_to_delete, 'customerName'].item()} 吗？此操作不可撤销。")
                if st.button("确认删除"):
                    try:
                        cursor.execute("SELECT * FROM cardInfo WHERE customerID=%s", (customer_id_to_delete,))
                        if cursor.fetchone():
                            st.error("删除失败：该客户名下还有银行卡，请先处理银行卡。")
                        else:
                            cursor.execute("DELETE FROM userInfo WHERE customerID=%s", (customer_id_to_delete,))
                            conn.commit()
                            st.success("客户删除成功！")
                            st.rerun()
                    except Exception as e:
                        conn.rollback()
                        st.error(f"删除失败: {e}")
            else:
                st.info("没有客户可供删除。")

        with tab_search:
            st.subheader("查询客户")
            search_term = st.text_input("输入客户姓名、身份证号或电话进行查询")
            if st.button("开始查询"):
                query = "SELECT * FROM userInfo WHERE customerName LIKE %s OR PID LIKE %s OR telephone LIKE %s"
                like_term = f"%{search_term}%"
                cursor.execute(query, (like_term, like_term, like_term))
                result = cursor.fetchall()
                if result:
                    st.dataframe(pd.DataFrame(result), use_container_width=True)
                else:
                    st.info("未查询到相关客户。")

    except Exception as e:
        st.error(f"发生错误: {e}")
    finally:
        cursor.close()
        conn.close()

# 银行卡管理
def manage_cards():
    st.header("银行卡信息管理")
    
    conn = get_connection()
    if not conn:
        return

    try:
        cursor = conn.cursor(dictionary=True)

        tab_view, tab_add, tab_edit, tab_delete, tab_search = st.tabs([
            "浏览银行卡", "新增银行卡", "管理银行卡状态", "注销银行卡", "查询银行卡"
        ])

        with tab_view:
            st.subheader("所有银行卡信息")
            cursor.execute("SELECT * FROM vw_customer_card ORDER BY customerID, cardID")
            cards = cursor.fetchall()
            if cards:
                df_cards = pd.DataFrame(cards)
                st.dataframe(df_cards, use_container_width=True)
                csv = df_cards.to_csv(index=False).encode('utf-8')
                st.download_button(
                    label="导出银行卡数据为CSV",
                    data=csv,
                    file_name='bank_cards.csv',
                    mime='text/csv',
                )
            else:
                st.info("系统中没有银行卡信息。")

        with tab_add:
            st.subheader("为客户新增银行卡")
            cursor.execute("SELECT customerID, customerName FROM userInfo")
            customers = cursor.fetchall()
            df_customers = pd.DataFrame(customers)
            
            cursor.execute("SELECT savingID, savingName FROM deposit")
            deposits = cursor.fetchall()
            df_deposits = pd.DataFrame(deposits)

            with st.form("new_card_form", clear_on_submit=True):
                customer_id = st.selectbox("选择客户", options=df_customers['customerID'], format_func=lambda x: df_customers[df_customers['customerID']==x]['customerName'].item())
                saving_id = st.selectbox("选择存款类型", options=df_deposits['savingID'], format_func=lambda x: df_deposits[df_deposits['savingID']==x]['savingName'].item())
                open_money = st.number_input("开户金额", min_value=1.0, step=10.0)
                password = st.text_input("初始密码 (6位数字)", value="888888", max_chars=6)
                
                submitted = st.form_submit_button("确认新增")
                if submitted:
                    try:
                        # cardID会自动生成，openDate会自动设置
                        query = "INSERT INTO cardInfo (customerID, savingID, openMoney, balance, pass) VALUES (%s, %s, %s, %s, %s)"
                        cursor.execute(query, (customer_id, saving_id, open_money, open_money, password))
                        conn.commit()
                        st.success("银行卡新增成功！")
                        st.rerun()
                    except Exception as e:
                        conn.rollback()
                        st.error(f"新增失败: {e}")

        with tab_edit:
            st.subheader("管理银行卡状态 (挂失/解挂)")
            cursor.execute("SELECT cardID, customerName, IsReportLoss FROM vw_customer_card")
            cards_for_edit = cursor.fetchall()
            if cards_for_edit:
                df_cards_edit = pd.DataFrame(cards_for_edit)
                card_id_to_edit = st.selectbox("选择银行卡", options=df_cards_edit['cardID'], format_func=lambda x: f"{x} - {df_cards_edit[df_cards_edit['cardID']==x]['customerName'].item()}")
                current_status = df_cards_edit[df_cards_edit['cardID'] == card_id_to_edit]['IsReportLoss'].item()
                
                st.write(f"当前状态: {'已挂失' if current_status else '正常'}")
                
                if not current_status:
                    if st.button("挂失此卡"):
                        cursor.execute("UPDATE cardInfo SET IsReportLoss = TRUE WHERE cardID = %s", (card_id_to_edit,))
                        conn.commit()
                        st.success("挂失成功！")
                        st.rerun()
                else:
                    if st.button("解除挂失"):
                        cursor.execute("UPDATE cardInfo SET IsReportLoss = FALSE WHERE cardID = %s", (card_id_to_edit,))
                        conn.commit()
                        st.success("解除挂失成功！")
                        st.rerun()
            else:
                st.info("没有银行卡可供管理。")

        with tab_delete:
            st.subheader("注销银行卡")
            cursor.execute("SELECT cardID, customerName, balance FROM vw_customer_card")
            cards_for_delete = cursor.fetchall()
            if cards_for_delete:
                df_cards_delete = pd.DataFrame(cards_for_delete)
                card_id_to_delete = st.selectbox("选择要注销的银行卡", options=df_cards_delete['cardID'], format_func=lambda x: f"{x} - {df_cards_delete[df_cards_delete['cardID']==x]['customerName'].item()}")
                balance = df_cards_delete[df_cards_delete['cardID']==card_id_to_delete]['balance'].item()
                st.warning(f"确定要注销卡号 {card_id_to_delete} 吗？该卡当前余额为: {balance}。注销前请确保余额已处理。")
                if st.button("确认注销"):
                    try:
                        # 为防止意外，先删除关联的交易记录
                        cursor.execute("DELETE FROM tradeInfo WHERE cardID=%s", (card_id_to_delete,))
                        cursor.execute("DELETE FROM cardInfo WHERE cardID=%s", (card_id_to_delete,))
                        conn.commit()
                        st.success(f"卡号 {card_id_to_delete} 已成功注销。")
                        st.rerun()
                    except Exception as e:
                        conn.rollback()
                        st.error(f"注销失败: {e}")
            else:
                st.info("没有银行卡可供注销。")
        
        with tab_search:
            st.subheader("查询银行卡")
            search_term = st.text_input("输入卡号或客户姓名进行查询")
            if st.button("开始查询", key="search_card_btn"):
                query = "SELECT * FROM vw_customer_card WHERE cardID LIKE %s OR customerName LIKE %s"
                like_term = f"%{search_term}%"
                cursor.execute(query, (like_term, like_term))
                result = cursor.fetchall()
                if result:
                    st.dataframe(pd.DataFrame(result), use_container_width=True)
                else:
                    st.info("未查询到相关银行卡。")

    except Exception as e:
        st.error(f"发生错误: {e}")
    finally:
        cursor.close()
        conn.close()

# 存款业务管理
def manage_deposits():
    st.header("存款业务管理")
    
    conn = get_connection()
    if not conn:
        return

    try:
        cursor = conn.cursor(dictionary=True)

        tab_view, tab_add, tab_edit, tab_delete = st.tabs([
            "浏览存款业务", "新增业务", "修改业务", "删除业务"
        ])

        with tab_view:
            st.subheader("所有存款业务类型")
            cursor.execute("SELECT * FROM deposit ORDER BY savingID")
            deposits = cursor.fetchall()
            if deposits:
                df_deposits = pd.DataFrame(deposits)
                st.dataframe(df_deposits, use_container_width=True)
                csv = df_deposits.to_csv(index=False).encode('utf-8')
                st.download_button(
                    label="导出存款业务为CSV",
                    data=csv,
                    file_name='deposit_types.csv',
                    mime='text/csv',
                )
            else:
                st.info("系统中没有存款业务信息。")

        with tab_add:
            st.subheader("新增存款业务")
            with st.form("new_deposit_form", clear_on_submit=True):
                name = st.text_input("业务名称")
                desc = st.text_area("业务描述")
                submitted = st.form_submit_button("确认新增")
                if submitted:
                    try:
                        cursor.execute("INSERT INTO deposit (savingName, descrip) VALUES (%s, %s)", (name, desc))
                        conn.commit()
                        st.success(f"存款业务 '{name}' 新增成功！")
                        st.rerun()
                    except Exception as e:
                        conn.rollback()
                        st.error(f"新增失败: {e}")
        
        with tab_edit:
            st.subheader("修改存款业务")
            cursor.execute("SELECT savingID, savingName FROM deposit")
            deposits_to_edit = cursor.fetchall()
            if deposits_to_edit:
                df_deposits_edit = pd.DataFrame(deposits_to_edit)
                deposit_id_to_edit = st.selectbox("选择要修改的业务", options=df_deposits_edit['savingID'], format_func=lambda x: df_deposits_edit[df_deposits_edit['savingID']==x]['savingName'].item())
                
                cursor.execute("SELECT * FROM deposit WHERE savingID=%s", (deposit_id_to_edit,))
                current_deposit = cursor.fetchone()

                with st.form("edit_deposit_form"):
                    name = st.text_input("新业务名称", value=current_deposit['savingName'])
                    desc = st.text_area("新业务描述", value=current_deposit['descrip'])
                    submitted = st.form_submit_button("确认修改")
                    if submitted:
                        try:
                            cursor.execute("UPDATE deposit SET savingName=%s, descrip=%s WHERE savingID=%s", (name, desc, deposit_id_to_edit))
                            conn.commit()
                            st.success(f"存款业务(ID: {deposit_id_to_edit})修改成功！")
                            st.rerun()
                        except Exception as e:
                            conn.rollback()
                            st.error(f"修改失败: {e}")
            else:
                st.info("没有可供修改的存款业务。")

        with tab_delete:
            st.subheader("删除存款业务")
            cursor.execute("SELECT savingID, savingName FROM deposit")
            deposits_to_delete = cursor.fetchall()
            if deposits_to_delete:
                df_deposits_delete = pd.DataFrame(deposits_to_delete)
                deposit_id_to_delete = st.selectbox("选择要删除的业务", options=df_deposits_delete['savingID'], format_func=lambda x: df_deposits_delete[df_deposits_delete['savingID']==x]['savingName'].item(), key="delete_deposit_select")
                st.warning(f"确定要删除业务 '{df_deposits_delete[df_deposits_delete['savingID']==deposit_id_to_delete]['savingName'].item()}' 吗？")
                if st.button("确认删除"):
                    try:
                        # 检查是否有银行卡正在使用此业务类型
                        cursor.execute("SELECT 1 FROM cardInfo WHERE savingID=%s LIMIT 1", (deposit_id_to_delete,))
                        if cursor.fetchone():
                            st.error("删除失败：有银行卡正在使用此存款业务类型。")
                        else:
                            cursor.execute("DELETE FROM deposit WHERE savingID=%s", (deposit_id_to_delete,))
                            conn.commit()
                            st.success(f"存款业务 '{df_deposits_delete[df_deposits_delete['savingID']==deposit_id_to_delete]['savingName'].item()}' 删除成功！")
                            st.rerun()
                    except Exception as e:
                        conn.rollback()
                        st.error(f"删除失败: {e}")
            else:
                st.info("没有可供删除的存款业务。")

    except Exception as e:
        st.error(f"发生错误: {e}")
    finally:
        cursor.close()
        conn.close()

# 交易管理
def manage_transactions():
    st.header("交易信息管理")
    
    conn = get_connection()
    if not conn:
        return

    try:
        cursor = conn.cursor(dictionary=True)

        tab_view, tab_delete, tab_search = st.tabs([
            "浏览所有交易", "删除交易", "查询交易"
        ])

        with tab_view:
            st.subheader("所有交易记录")
            # 使用视图来获取更友好的信息
            cursor.execute("SELECT * FROM vw_transaction_details ORDER BY tradeDate DESC")
            transactions = cursor.fetchall()
            if transactions:
                df_transactions = pd.DataFrame(transactions)
                st.dataframe(df_transactions, use_container_width=True)
                csv = df_transactions.to_csv(index=False).encode('utf-8')
                st.download_button(
                    label="导出交易记录为CSV",
                    data=csv,
                    file_name='transactions.csv',
                    mime='text/csv',
                )
            else:
                st.info("系统中没有交易记录。")

        with tab_delete:
            st.subheader("删除交易记录")
            st.warning("警告：删除交易记录是危险操作，可能导致账目不平，请谨慎操作！")
            
            cursor.execute("SELECT tradeID, cardID, customerName, tradeType, tradeMoney, tradeDate FROM vw_transaction_details")
            transactions_to_delete = cursor.fetchall()
            if transactions_to_delete:
                df_tx_delete = pd.DataFrame(transactions_to_delete)
                tx_id_to_delete = st.selectbox(
                    "选择要删除的交易", 
                    options=df_tx_delete['tradeID'], 
                    format_func=lambda x: f"ID:{x} - {df_tx_delete[df_tx_delete['tradeID']==x]['customerName'].item()} - {df_tx_delete[df_tx_delete['tradeID']==x]['tradeType'].item()} {df_tx_delete[df_tx_delete['tradeID']==x]['tradeMoney'].item()}元"
                )
                if st.button("确认删除此交易"):
                    try:
                        cursor.execute("DELETE FROM tradeInfo WHERE tradeID=%s", (tx_id_to_delete,))
                        conn.commit()
                        st.success(f"交易记录(ID: {tx_id_to_delete})删除成功。")
                        st.rerun()
                    except Exception as e:
                        conn.rollback()
                        st.error(f"删除失败: {e}")
            else:
                st.info("没有可供删除的交易。")

        with tab_search:
            st.subheader("查询交易记录")
            search_term = st.text_input("输入卡号、客户姓名或备注进行查询")
            if st.button("开始查询", key="search_tx_btn"):
                query = """
                    SELECT * FROM vw_transaction_details 
                    WHERE cardID LIKE %s 
                    OR customerName LIKE %s 
                    OR remark LIKE %s
                """
                like_term = f"%{search_term}%"
                cursor.execute(query, (like_term, like_term, like_term))
                result = cursor.fetchall()
                if result:
                    st.dataframe(pd.DataFrame(result), use_container_width=True)
                else:
                    st.info("未查询到相关交易。")

    except Exception as e:
        st.error(f"发生错误: {e}")
    finally:
        cursor.close()
        conn.close() 